import mysql from 'mysql2/promise';
import dotenv from 'dotenv';

dotenv.config();

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '1221',
  database: process.env.DB_NAME || 'english_learning',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

// 创建连接池（不指定数据库）
const pool = mysql.createPool({
  ...dbConfig,
  database: undefined
});

// 创建数据库
const createDatabase = async () => {
  try {
    await pool.query(`CREATE DATABASE IF NOT EXISTS ${dbConfig.database}`);
    console.log(`数据库 ${dbConfig.database} 创建成功或已存在`);
    
    // 切换到指定数据库
    await pool.query(`USE ${dbConfig.database}`);
    console.log(`已切换到数据库 ${dbConfig.database}`);
  } catch (error) {
    console.error('创建数据库失败:', error);
    throw error;
  }
};

// 测试数据库连接
export const testConnection = async () => {
  try {
    // 先创建数据库
    await createDatabase();
    
    const connection = await pool.getConnection();
    console.log('数据库连接成功');
    connection.release();
    return true;
  } catch (error) {
    console.error('数据库连接失败:', error);
    return false;
  }
};

// 执行SQL查询
export const query = async <T = any>(sql: string, params?: any[]): Promise<[T[], any]> => {
  try {
    const [results, fields] = await pool.execute(sql, params);
    return [results as T[], fields];
  } catch (error) {
    console.error('SQL查询错误:', error);
    throw error;
  }
};

// 导出连接池
export const getPool = () => pool;

// 导出数据库配置
export const getDbConfig = () => dbConfig; 